const sqlite3 = require('../server/node_modules/sqlite3').verbose();
const path = require('path');

// 创建数据库连接
const dbPath = path.join(__dirname, '../resources/db/db.database');
console.log('Database path:', dbPath);

const db = new sqlite3.Database(dbPath);

// 查询user_lesson_progress表中的数据
db.serialize(() => {
  console.log('\n=== User Lesson Progress Data ===');
  db.each(`
    SELECT id, user_id, lesson_id, course_id, is_completed, completion_percentage, time_spent, created_at, updated_at
    FROM user_lesson_progress
  `, (err, row) => {
    if (err) {
      console.error('Error:', err);
    } else {
      console.log(row);
    }
  }, () => {
    // 查询完成后继续查询user_course_progress表
    console.log('\n=== User Course Progress Data ===');
    db.each(`
      SELECT id, user_id, course_id, is_completed, completion_percentage, created_at, updated_at
      FROM user_course_progress
    `, (err, row) => {
      if (err) {
        console.error('Error:', err);
      } else {
        console.log(row);
      }
    }, () => {
      // 查询统计信息
      console.log('\n=== Statistics Query Analysis ===');
      const userId = 1; // 假设用户ID为1
      db.get(`
        SELECT 
          (SELECT COUNT(*) FROM user_course_progress WHERE user_id = ?) as courses_enrolled,
          (SELECT COUNT(*) FROM user_course_progress WHERE user_id = ? AND is_completed = 1) as courses_completed,
          (SELECT COUNT(*) FROM user_lesson_progress WHERE user_id = ? AND is_completed = 1) as lessons_completed,
          (SELECT COUNT(*) FROM user_vocabulary WHERE user_id = ? AND is_learned = 1) as words_learned,
          (SELECT COUNT(*) FROM user_sentence_progress WHERE user_id = ? AND is_mastered = 1) as sentences_mastered,
          (SELECT SUM(time_spent) FROM user_lesson_progress WHERE user_id = ?) as total_study_time
      `, [userId, userId, userId, userId, userId, userId], (err, row) => {
        if (err) {
          console.error('Error:', err);
        } else {
          console.log('Statistics for user_id =', userId);
          console.log(row);
        }
        db.close();
      });
    });
  });
});